ALTER  VIEW [dbo].[vw_MarkansPack_Wip_View] AS(
SELECT  
	LT.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER,FAB_LOT_NUMBER, ISNULL(LOT_CATEGORY,'PROD') AS LOT_CATEGORY, 
	QTY_IN, DATE_IN, LOT_CURRENT_STATUS,PART_NUMBER,
	(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
		CASE 
			WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
			WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
		END 
	) AS BASE_PRODUCT,
	(CASE 
		WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
		WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
	END) AS BASE_PRODUCT_ID ,PS_TYPE_NAME, PS_TYPE, 8 AS PS_TYPE_ID, LT.LOT_ID, LT.LOCATION_ID,
	/*(CASE
		WHEN ( PARAMETER_VALUE IS NULL OR PARAMETER_VALUE = '' ) THEN 'TCP'
		ELSE
		PARAMETER_VALUE
	END)*/ NULL AS PACKAGE_TYPE
FROM 
	LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS,LOCATION L
WHERE 
	LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
	AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
	AND LT.LOCATION_ID = L.LOCATION_ID
	AND  QTY_IN > 0 
	AND ( LOT_CURRENT_STATUS = 'Running' OR LOT_CURRENT_STATUS = 'Hold' )
	--AND P.OBSOLETE != 0
	AND PS_TYPE_NAME = 'Mark & Pack'
)
